'\" t
.\"     Title: VACUUM
.\"    Author: The PostgreSQL Global Development Group
.\" Generator: DocBook XSL Stylesheets v1.75.2 <http://docbook.sf.net/>
.\"      Date: 2011-12-01
.\"    Manual: PostgreSQL 9.1.2 Documentation
.\"    Source: PostgreSQL 9.1.2
.\"  Language: English
.\"
.TH "VACUUM" "7" "2011-12-01" "PostgreSQL 9.1.2" "PostgreSQL 9.1.2 Documentation"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
VACUUM \- garbage\-collect and optionally analyze a database
.\" VACUUM
.SH "SYNOPSIS"
.sp
.nf
VACUUM [ ( { FULL | FREEZE | VERBOSE | ANALYZE } [, \&.\&.\&.] ) ] [ \fItable\fR [ (\fIcolumn\fR [, \&.\&.\&.] ) ] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ \fItable\fR ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ \fItable\fR [ (\fIcolumn\fR [, \&.\&.\&.] ) ] ]
.fi
.SH "DESCRIPTION"
.PP

VACUUM
reclaims storage occupied by dead tuples\&. In normal
PostgreSQL
operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a
VACUUM
is done\&. Therefore it\*(Aqs necessary to do
VACUUM
periodically, especially on frequently\-updated tables\&.
.PP
With no parameter,
VACUUM
processes every table in the current database that the current user has permission to vacuum\&. With a parameter,
VACUUM
processes only that table\&.
.PP

VACUUM ANALYZE
performs a
VACUUM
and then an
ANALYZE
for each selected table\&. This is a handy combination form for routine maintenance scripts\&. See
\fBANALYZE\fR(7)
for more details about its processing\&.
.PP
Plain
VACUUM
(without
FULL) simply reclaims space and makes it available for re\-use\&. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained\&. However, extra space is not returned to the operating system (in most cases); it\*(Aqs just kept available for re\-use within the same table\&.
VACUUM FULL
rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system\&. This form is much slower and requires an exclusive lock on each table while it is being processed\&.
.PP
When the option list is surrounded by parentheses, the options can be written in any order\&. Without parentheses, options must be specified in exactly the order shown above\&. The parenthesized syntax was added in
PostgreSQL
9\&.0; the unparenthesized syntax is deprecated\&.
.SH "PARAMETERS"
.PP
FULL
.RS 4
Selects
\(lqfull\(rq
vacuum, which can reclaim more space, but takes much longer and exclusively locks the table\&. This method also requires extra disk space, since it writes a new copy of the table and doesn\*(Aqt release the old copy until the operation is complete\&. Usually this should only be used when a significant amount of space needs to be reclaimed from within the table\&.
.RE
.PP
FREEZE
.RS 4
Selects aggressive
\(lqfreezing\(rq
of tuples\&. Specifying
FREEZE
is equivalent to performing
VACUUM
with the
vacuum_freeze_min_age
parameter set to zero\&.
.RE
.PP
VERBOSE
.RS 4
Prints a detailed vacuum activity report for each table\&.
.RE
.PP
ANALYZE
.RS 4
Updates statistics used by the planner to determine the most efficient way to execute a query\&.
.RE
.PP
\fItable\fR
.RS 4
The name (optionally schema\-qualified) of a specific table to vacuum\&. Defaults to all tables in the current database\&.
.RE
.PP
\fIcolumn\fR
.RS 4
The name of a specific column to analyze\&. Defaults to all columns\&. If a column list is specified,
ANALYZE
is implied\&.
.RE
.SH "OUTPUTS"
.PP
When
VERBOSE
is specified,
VACUUM
emits progress messages to indicate which table is currently being processed\&. Various statistics about the tables are printed as well\&.
.SH "NOTES"
.PP
To vacuum a table, one must ordinarily be the table\*(Aqs owner or a superuser\&. However, database owners are allowed to vacuum all tables in their databases, except shared catalogs\&. (The restriction for shared catalogs means that a true database\-wide
VACUUM
can only be performed by a superuser\&.)
VACUUM
will skip over any tables that the calling user does not have permission to vacuum\&.
.PP

VACUUM
cannot be executed inside a transaction block\&.
.PP
For tables with
GIN
indexes,
VACUUM
(in any form) also completes any pending index insertions, by moving pending index entries to the appropriate places in the main
GIN
index structure\&. See
Section 54.3.1, \(lqGIN Fast Update Technique\(rq, in the documentation
for details\&.
.PP
We recommend that active production databases be vacuumed frequently (at least nightly), in order to remove dead rows\&. After adding or deleting a large number of rows, it might be a good idea to issue a
VACUUM ANALYZE
command for the affected table\&. This will update the system catalogs with the results of all recent changes, and allow the
PostgreSQL
query planner to make better choices in planning queries\&.
.PP
The
\fBFULL\fR
option is not recommended for routine use, but might be useful in special cases\&. An example is when you have deleted or updated most of the rows in a table and would like the table to physically shrink to occupy less disk space and allow faster table scans\&.
VACUUM FULL
will usually shrink the table more than a plain
VACUUM
would\&.
.PP

VACUUM
causes a substantial increase in I/O traffic, which might cause poor performance for other active sessions\&. Therefore, it is sometimes advisable to use the cost\-based vacuum delay feature\&. See
Section 18.4.3, \(lqCost-based Vacuum Delay\(rq, in the documentation
for details\&.
.PP

PostgreSQL
includes an
\(lqautovacuum\(rq
facility which can automate routine vacuum maintenance\&. For more information about automatic and manual vacuuming, see
Section 23.1, \(lqRoutine Vacuuming\(rq, in the documentation\&.
.SH "EXAMPLES"
.PP
The following is an example from running
VACUUM
on a table in the regression database:
.sp
.if n \{\
.RS 4
.\}
.nf
regression=# VACUUM (VERBOSE, ANALYZE) onek;
INFO:  vacuuming "public\&.onek"
INFO:  index "onek_unique1" now contains 1000 tuples in 14 pages
DETAIL:  3000 index tuples were removed\&.
0 index pages have been deleted, 0 are currently reusable\&.
CPU 0\&.01s/0\&.08u sec elapsed 0\&.18 sec\&.
INFO:  index "onek_unique2" now contains 1000 tuples in 16 pages
DETAIL:  3000 index tuples were removed\&.
0 index pages have been deleted, 0 are currently reusable\&.
CPU 0\&.00s/0\&.07u sec elapsed 0\&.23 sec\&.
INFO:  index "onek_hundred" now contains 1000 tuples in 13 pages
DETAIL:  3000 index tuples were removed\&.
0 index pages have been deleted, 0 are currently reusable\&.
CPU 0\&.01s/0\&.08u sec elapsed 0\&.17 sec\&.
INFO:  index "onek_stringu1" now contains 1000 tuples in 48 pages
DETAIL:  3000 index tuples were removed\&.
0 index pages have been deleted, 0 are currently reusable\&.
CPU 0\&.01s/0\&.09u sec elapsed 0\&.59 sec\&.
INFO:  "onek": removed 3000 tuples in 108 pages
DETAIL:  CPU 0\&.01s/0\&.06u sec elapsed 0\&.07 sec\&.
INFO:  "onek": found 3000 removable, 1000 nonremovable tuples in 143 pages
DETAIL:  0 dead tuples cannot be removed yet\&.
There were 0 unused item pointers\&.
0 pages are entirely empty\&.
CPU 0\&.07s/0\&.39u sec elapsed 1\&.56 sec\&.
INFO:  analyzing "public\&.onek"
INFO:  "onek": 36 pages, 1000 rows sampled, 1000 estimated total rows
VACUUM
.fi
.if n \{\
.RE
.\}
.SH "COMPATIBILITY"
.PP
There is no
VACUUM
statement in the SQL standard\&.
.SH "SEE ALSO"
\fBvacuumdb\fR(1), Section 18.4.3, \(lqCost-based Vacuum Delay\(rq, in the documentation, Section 23.1.5, \(lqThe Autovacuum Daemon\(rq, in the documentation
